package com.example.examsystem.repository;

import com.example.examsystem.model.Notice;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.time.LocalDateTime;
import java.util.List;

/**
 * 通知公告数据访问层
 */
@Repository
public interface NoticeRepository extends JpaRepository<Notice, Long> {
    
    /**
     * 根据状态查询通知列表
     */
    List<Notice> findByStatus(String status);
    
    /**
     * 根据类型查询通知列表
     */
    List<Notice> findByType(String type);
    
    /**
     * 查询置顶通知
     */
    List<Notice> findByIsTopTrueAndStatusOrderByCreatedAtDesc(String status);
    
    /**
     * 查询紧急通知
     */
    List<Notice> findByIsUrgentTrueAndStatusOrderByCreatedAtDesc(String status);
    
    /**
     * 分页查询通知（支持多条件筛选）
     */
    @Query("""
       SELECT n FROM Notice n
       WHERE
           (:title IS NULL OR :title = '' OR LOWER(n.title) LIKE LOWER(CONCAT('%', :title, '%'))) AND
           (:type IS NULL OR :type = '' OR n.type = :type) AND
           (:status IS NULL OR :status = '' OR n.status = :status) AND
           (:targetType IS NULL OR :targetType = '' OR n.targetType = :targetType) AND
           (:isTop IS NULL OR n.isTop = :isTop) AND
           (:isUrgent IS NULL OR n.isUrgent = :isUrgent) AND
           (:createdBy IS NULL OR n.createdBy = :createdBy)
       """)
    Page<Notice> findByQuery(@Param("title") String title,
                            @Param("type") String type,
                            @Param("status") String status,
                            @Param("targetType") String targetType,
                            @Param("isTop") Boolean isTop,
                            @Param("isUrgent") Boolean isUrgent,
                            @Param("createdBy") Long createdBy,
                            Pageable pageable);
    
    /**
     * 查询用户可见的通知（简化版本）
     */
    @Query("""
       SELECT n FROM Notice n
       WHERE
           n.status = :status AND
           (n.publishTime IS NULL OR n.publishTime <= :now) AND
           (n.expireTime IS NULL OR n.expireTime > :now) AND
           (n.targetType = 'ALL' OR
            (n.targetType = 'USER' AND n.targetIds LIKE CONCAT('%', CAST(:userId AS string), '%')) OR
            (n.targetType = 'ROLE' AND n.targetIds LIKE CONCAT('%', :userRole, '%')))
       """)
    Page<Notice> findVisibleNotices(@Param("userId") Long userId,
                                   @Param("userRole") String userRole,
                                   @Param("status") String status,
                                   @Param("now") LocalDateTime now,
                                   Pageable pageable);
    
    /**
     * 查询有效的通知（已发布且未过期）
     */
    @Query("""
       SELECT n FROM Notice n
       WHERE
           n.status = 'PUBLISHED' AND
           (n.publishTime IS NULL OR n.publishTime <= :now) AND
           (n.expireTime IS NULL OR n.expireTime > :now)
       """)
    List<Notice> findActiveNotices(@Param("now") LocalDateTime now);
    
    /**
     * 查询过期的通知
     */
    @Query("""
       SELECT n FROM Notice n
       WHERE
           n.status = 'PUBLISHED' AND
           n.expireTime IS NOT NULL AND n.expireTime <= :now
       """)
    List<Notice> findExpiredNotices(@Param("now") LocalDateTime now);
    
    /**
     * 增加浏览次数
     */
    @Modifying
    @Query("UPDATE Notice n SET n.viewCount = n.viewCount + 1 WHERE n.id = :id")
    void incrementViewCount(@Param("id") Long id);
    
    /**
     * 批量更新状态
     */
    @Modifying
    @Query("""
       UPDATE Notice n
       SET n.status = :status, n.updatedAt = :now
       WHERE n.id IN :ids
       """)
    void batchUpdateStatus(@Param("ids") List<Long> ids, @Param("status") String status, @Param("now") LocalDateTime now);
    
    /**
     * 自动归档过期通知
     */
    @Modifying
    @Query("""
       UPDATE Notice n
       SET n.status = 'ARCHIVED', n.updatedAt = :now
       WHERE
           n.status = 'PUBLISHED' AND
           n.expireTime IS NOT NULL AND n.expireTime <= :now
       """)
    int autoArchiveExpiredNotices(@Param("now") LocalDateTime now);
}
